package com.eascs.web.o2o.web.aging.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.aging.dao.AgingDao;
import com.eascs.web.o2o.web.aging.entity.NodeEntity;
import com.eascs.web.o2o.web.aging.entity.ProcessInfoEntity;
import com.eascs.web.o2o.web.aging.entity.WorkFlowEntity;
import com.eascs.web.o2o.web.aging.form.AgingQueryForm;
import com.eascs.web.o2o.web.aging.form.NodeAgingForm;
import com.eascs.web.o2o.web.aging.form.ProcessAgingForm;
import com.eascs.web.o2o.web.aging.service.IAgingService;
import com.eascs.web.o2o.web.dao.BiodsDao;

@Service
public class AgingServiceImpl implements IAgingService {

	private static Logger logger = LoggerFactory.getLogger(AgingServiceImpl.class);
	
	private static final String PROCESS_TASK_MAIN_TABLE = "ods_workflow_process_task_main";
	private static final String DICTIONARY_DATA_TABLE = "ods_o2oworkflow_wf_dictionary_data";
	private static final String PROCESS_TASK_NODE_TABLE = "ods_workflow_process_task_node";
	private static final String TASK_NODE_AUDITORS_TABLE = "ods_workflow_task_node_auditors";
	private static final String O2O_PLATFORM_REGIONAL_TABLE = "ods_o2odev_platform_regional";
	private static final String NODE_TIME_PROCESSED_TABLE = "dm_node_time_processed";

	@Autowired
	AgingDao agingDao;

	@Autowired
	BiodsDao biodsDao;
	@Override
	public PageResult<Object[]> getAgingInfoByPage(AgingQueryForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getAgingIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT wf.refer_code , wf.org_name , wf.appliedby_name,wf.used_time as time1 ,wf.used_time as time2,wf.used_time as time3,wf.used_time as time4 , wf.overtime  FROM "+PROCESS_TASK_MAIN_TABLE+" wf WHERE 1=1 ");
		
		if(!StringUtil.isEmpty(entity.getProcedureType())){
			querySql.append(" AND wf.process_type = ? "); 
			sqlPageQuery.addParam(entity.getProcedureType());
		}
		if(!StringUtil.isEmpty(entity.getBillCode())){
			querySql.append(" AND wf.refer_code = ? "); 
			sqlPageQuery.addParam(entity.getBillCode());
		}
		if(!StringUtil.isEmpty(entity.getCreateDateStart())){
			querySql.append(" AND wf.applied_date > ? ");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if(!StringUtil.isEmpty(entity.getCreateDateEnd())){
			querySql.append(" AND wf.applied_date < ? ");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}
		if(!StringUtil.isEmpty(entity.getLtdName())){
			querySql.append(" AND wf.org_name like ? ");
			sqlPageQuery.addLikeParam(entity.getLtdName());
		}
		if (entity.getOverIndex()) {
			querySql.append(" AND wf.overtime > 0  ");
		}
		querySql.append(" AND wf.process_status != '3' group by wf.refer_code  ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public List<ProcessInfoEntity> getProcess() {
		return biodsDao.findEntitys("SELECT wd.code , wd.name  FROM "+DICTIONARY_DATA_TABLE+" wd WHERE wd.is_lock = 'N' AND wd.type = ? ", ProcessInfoEntity.class, new Object[]{10});
	}

	@Override
	public PageResult<Object[]> getAgingDetail(String billCode, String processType) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT node_name , auditor_start_date , auditor_end_date , ");
		querySql.append(" used_time , time_effective , ");
		querySql.append(" case when(used_time - time_effective) < 0 then 0 else (used_time - time_effective) end ");
		querySql.append(" from "+PROCESS_TASK_NODE_TABLE+" WHERE task_id = ");
		querySql.append("( SELECT task_id FROM "+PROCESS_TASK_MAIN_TABLE+" WHERE refer_code = ? AND process_type = ? AND process_status != '3' )  ");
		sqlPageQuery.addParam(StringUtil.trimBlank(billCode));
		sqlPageQuery.addParam(StringUtil.trimBlank(processType));
		querySql.append(" and node_status != '5' ORDER BY node_seq ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public PageResult<WorkFlowEntity> getWorkFlow() {
		SqlPageQuery<WorkFlowEntity> sqlPageQuery = new SqlPageQuery<>(WorkFlowEntity.class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT  m.refer_code as billCode , m.refer_id as referId, M.TASK_ID as taskId,M.PROCESS_NAME as processName,M.PROCESS_TYPE as processType,M.TIME_EFFECTIVE as timeEffective,"
				+ " M.USED_TIME usedTime,M.OVERTIME as overtime FROM "+PROCESS_TASK_MAIN_TABLE+" M WHERE M.PROCESS_STATUS != '3'";
		sqlPageQuery.setQuerySql(querySql);
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public PageResult<NodeEntity> getNode() {
		SqlPageQuery<NodeEntity> sqlPageQuery = new SqlPageQuery<>(NodeEntity.class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT N.TASK_ID as taskId, N.NODE_NAME as nodeName , N.AUDITOR_START_DATE as auditorStartDate , N.AUDITOR_END_DATE as auditorEndDate , "
				+ " N.TIME_EFFECTIVE as timeEffective , N.USED_TIME as usedTime FROM "+PROCESS_TASK_NODE_TABLE+" N WHERE N.NODE_STATUS != '5' ";
		sqlPageQuery.setQuerySql(querySql);
		return biodsDao.findDataWithPage(sqlPageQuery);
	}
	
	

	@Override
	public PageResult<Object[]> listNodeAgingInfoFinished(NodeAgingForm entity){
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name,s.node_name,s.auditor_name,s.auditor_start_date,s.auditor_date,s.node_time_consuming");
		}else{
			sql.append("select s.task_id,s.task_code,s.process_name,s.node_name,s.auditor_name, s.auditor_start_date,s.auditor_date,s.node_time_consuming");
		}
		sql.append("  from "+NODE_TIME_PROCESSED_TABLE+" s where 1=1");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (StringUtil.hasText(entity.getNodeStatus())) {
			sql.append(" and s.node_status_name = ? ");
			pageQuery.addParam(entity.getNodeStatus());
		}
		if (StringUtil.hasText(entity.getNodesNa())) {
			sql.append(" and s.node_name like ? ");
			pageQuery.addParam("%" + entity.getNodesNa() + "%");
		}
		if (StringUtil.hasText(entity.getNodeAuditors())) {
			sql.append(" and s.auditor_name like ?");
			pageQuery.addParam("%" + entity.getNodeAuditors() + "%");
		}

		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_start_date >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		
		if (!StringUtil.isEmpty(entity.getNodeTimeS())) {
			sql.append(" AND  s.m >= ?");
			pageQuery.addParam( Float.valueOf(entity.getNodeTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getNodeTimeE())) {
			sql.append(" AND s.m < ?");
			pageQuery.addParam(Float.valueOf(entity.getNodeTimeE()));
		}
		sql.append(" order by s.auditor_date desc ");
		
		logger.info("sql:{}", sql.toString());
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> listNodeAgingInfoUnfinished(NodeAgingForm entity){
		
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name, s.node_name,s.auditor_name, s.auditor_start_date,s.auditor_date, s.node_time_consume ");
		}else{
			sql.append("select s.task_id,s.task_code,s.process_name,s.node_name,s.auditor_name, s.auditor_start_date,s.auditor_date,s.node_time_consume");
		}
		sql.append("  from dm_node_time_unprocessed s where 1=1 ");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (StringUtil.hasText(entity.getNodeStatus())) {
			sql.append(" and s.node_status_name = ? ");
			pageQuery.addParam(entity.getNodeStatus());
		}
		if (StringUtil.hasText(entity.getNodesNa())) {
			sql.append(" and s.node_name like ? ");
			pageQuery.addParam("%" + entity.getNodesNa() + "%");
		}
		if (StringUtil.hasText(entity.getNodeAuditors())) {
			sql.append(" and s.auditor_name like ?");
			pageQuery.addParam("%" + entity.getNodeAuditors() + "%");
		}
		
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_start_date >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		
		if (!StringUtil.isEmpty(entity.getNodeTimeS())) {
			sql.append(" AND  s.node_time_consume >= ?");
			pageQuery.addParam( Float.valueOf(entity.getNodeTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getNodeTimeE())) {
			sql.append(" AND s.node_time_consume < ?");
			pageQuery.addParam(Float.valueOf(entity.getNodeTimeE()));
		}
		sql.append("  order by s.node_time_consume desc ");
		logger.info("节点时效未处理sql：{}", sql.toString());
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	
	
	
	@Override
	public PageResult<Object[]> listProcessAgingInfoFinished(ProcessAgingForm entity){
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name, date_format(s.auditor_stime,'%Y-%m-%d %H:%i:%s') auditor_stime,date_format(s.auditor_etime,'%Y-%m-%d %H:%i:%s'),  s.process_time_consume ");
		}else{
			sql.append("select s.task_id, s.task_code,s.province_name,s.process_name, s.auditor_stime,s.auditor_etime,  s.process_time_consume  ");
		}
		sql.append(" from dm_process_time_processed s where 1=1 ");
		
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getDateStart())) {
			sql.append(" AND  s.auditor_stime >= ?");
			pageQuery.addParam(entity.getDateStart());
		}
		if (!StringUtil.isEmpty(entity.getDateEnd())) {
			sql.append(" AND  s.auditor_stime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEnd());
		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_etime >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND  s.auditor_etime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeS())) {
			sql.append(" AND  s.process_time_consume >= ?");
			pageQuery.addParam( Float.valueOf(entity.getProcessTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeE())) {
			sql.append(" AND s.process_time_consume < ?");
			pageQuery.addParam(Float.valueOf(entity.getProcessTimeE()));
		}
		if (!StringUtil.isEmpty(entity.getProvince())) {
			sql.append(" AND s.province_code = ?");
			pageQuery.addParam(entity.getProvince());
		}
		sql.append(" order by s.auditor_etime desc ");
		pageQuery.setQuerySql(sql.toString());
		logger.info("流程时效已处理sql:{}", sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	@Override
	public PageResult<Object[]> listProcessAgingInfoUnfinished(ProcessAgingForm entity){
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name, date_format(s.auditor_stime,'%Y-%m-%d %H:%i:%s') auditor_stime,date_format(s.auditor_etime,'%Y-%m-%d %H:%i:%s'),  s.process_time_consume ");
		}else{
			sql.append("select s.task_id, s.task_code,s.province_name,s.process_name, s.auditor_stime,s.auditor_etime,  s.process_time_consume  ");
		}
		sql.append(" from dm_process_time_unprocessed s where 1=1 ");
		
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getDateStart())) {
			sql.append(" AND  s.auditor_stime >= ?");
			pageQuery.addParam(entity.getDateStart());
		}
		if (!StringUtil.isEmpty(entity.getDateEnd())) {
			sql.append(" AND  s.auditor_stime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEnd());
		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_etime >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND  s.auditor_etime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeS())) {
			sql.append(" AND  s.process_time_consume >= ?");
			pageQuery.addParam( Float.valueOf(entity.getProcessTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeE())) {
			sql.append(" AND s.process_time_consume < ?");
			pageQuery.addParam(Float.valueOf(entity.getProcessTimeE()));
		}
		if (!StringUtil.isEmpty(entity.getProvince())) {
			sql.append(" AND s.province_code = ?");
			pageQuery.addParam(entity.getProvince());
		}
		sql.append(" order by s.auditor_etime desc ");
		pageQuery.setQuerySql(sql.toString());
		logger.info("流程时效未处理sql：{}", sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	@Override
	public List<Object[]> getProcessType(String referId) {
		return biodsDao.findObjectsList("select process_type from "+PROCESS_TASK_MAIN_TABLE+"  where refer_id = ? ", referId);
	}
	
}
